package com.freework.sys.dic.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.freework.Cfg;
import com.freework.freedbm.base.DTO;
import com.freework.freedbm.cfg.fieldcfg.FieldInfoEnum;
import com.freework.freedbm.cfg.fieldcfg.type.SQLType;
import com.freework.freedbm.cfg.tablecfg.TableDataManager;
import com.freework.freedbm.cfg.tablecfg.TableQuery;
import com.freework.freedbm.dao.DaoSupport;
import com.freework.freedbm.dao.JdbcForObject;
import com.freework.freedbm.dao.TotalResult;
import com.freework.freedbm.dao.jdbcm.JdbcForDTO;
import com.freework.freedbm.dao.jdbcm.Param;
import com.freework.freedbm.dao.jdbcm.QueryInfo;
import com.freework.freedbm.util.DTOByCfg;
import com.freework.freedbm.util.Tool;
import com.freework.sys.dic.vo.DICVOCfg;
import com.freework.sys.dic.vo.DicVO;
import com.freework.sys.table.vo.SysDictableVO;
import com.freework.util.SqlUtil;
@Service
public class DicDao extends DaoSupport<DicVO ,FieldInfoEnum>{
	
	
	public DicVO getDicVOByCode(SysDictableVO table,Object code){
		QueryInfo qi=QueryInfo.getQueryInfo(table.dicManager(), table.getCodecolumn()+"=?", code.toString());
		return (DicVO) this.getDTOObject(qi);
	}
	
	public DicVO getDicVOByCode(SysDictableVO table,Object code,Object groupcode){
		QueryInfo qi=QueryInfo.getQueryInfo(table.dicManager(), table.getCodecolumn()+"=? and "+table.getGroupcodecolumn()+"=? ", code.toString(),groupcode.toString());
		return (DicVO) this.getDTOObject(qi);
	}
	public DicVO getDicVOById(SysDictableVO table,Object id){
		QueryInfo qi=QueryInfo.getQueryInfo(table.dicManager(), table.getKeycolumn()+"=?", id.toString());
		return (DicVO) this.getDTOObject(qi);
	}
	public List<DicVO>  findTableDateByGroupcode(SysDictableVO table,Object groupcode){
		DicVO vo=new DicVO((DICVOCfg) table.dicManager());
		vo.put(table.getGroupcodecolumn(), groupcode.toString());
		return this.query(vo);
	}
	public List<DicVO>  findTableDateByWhere(SysDictableVO table,String where){
		DicVO vo=new DicVO((DICVOCfg) table.dicManager());
		return this.query(vo,where);
	}
	public List<Object> getIdsByWhere(SysDictableVO table,String where){
		StringBuilder sql=new StringBuilder("select ").append(table.getKeycolumn()).append(" from ").append(table.getTablename()).append(" where ").append(where);
		final JdbcForDTO key=table.dicManager().getPKey()[0];
		return this.getJdbcTemplate().query(sql.toString(), new ResultSetExtractor<List<Object>>(){
			public List<Object> extractData(final ResultSet rs) throws SQLException,
					DataAccessException {
				List<Object> list=new LinkedList<Object>();
				while(rs.next()){
					list.add(((SQLType)key.getType()).get(rs, 1));
				}
				return list;
			}
			
		});
		
	}
	
	public Integer getCount(DicVO dto,SysDictableVO table){
		String tablename=DTOByCfg.getTableDataManager(dto).getTableName();
		
		QueryInfo count =null;
		if(table.getGroupcodecolumn()!=null&&!table.getGroupcodecolumn().equals("")){
			String querySelect="select count(*) from "+tablename+" where "+table.getCodecolumn()+"=? and "+table.getGroupcodecolumn()+"=?";
			count =QueryInfo.getQueryInfo(querySelect, Cfg.Integer, dto.getCode(),dto.get(table.getGroupcodecolumn()));
		}else{	
			String querySelect="select count(*) from "+tablename+" where "+table.getCodecolumn()+"=?";
			count =QueryInfo.getQueryInfo(querySelect, Cfg.Integer, dto.getCode());
		}
		Integer countInt=(Integer) this.getObject(count);
		return countInt;
	} 
	
	public Integer getCountNotThis(DicVO dto,SysDictableVO table){
		String tablename=DTOByCfg.getTableDataManager(dto).getTableName();
		
		QueryInfo count =null;
		if(table.getGroupcodecolumn()!=null&&!table.getGroupcodecolumn().equals("")){
			String querySelect="select count(*) from "+tablename+" where "+table.getCodecolumn()+"=? and "+table.getGroupcodecolumn()+"=? and "+table.getKeycolumn()+"<>?";
			count =QueryInfo.getQueryInfo(querySelect, Cfg.Integer, dto.getCode(),dto.get(table.getGroupcodecolumn()),dto.getId());
		}else{	
			String querySelect="select count(*) from "+tablename+" where "+table.getCodecolumn()+"=? and "+table.getKeycolumn()+"<>?";
			count =QueryInfo.getQueryInfo(querySelect, Cfg.Integer, dto.getCode(),dto.getId());
		}
		Integer countInt=(Integer) this.getObject(count);
		return countInt;
	} 
	
	public int delete(TableDataManager cfg,String[] ids){
		if(ids.length==0)
			return 0; 
		String tablename=cfg.getTableName();
		JdbcForDTO key=cfg.getPKey()[0];
		SQLType keyType=(SQLType) key.getType();
		String where=getWhere(key.getColName(),ids.length);
		String deleteSQl="delete from "+tablename+" where "+where;
		System.out.println(deleteSQl);
		Object[] obj=Tool.convertArray(keyType.getReturnedClass(), ids);
		return this.update(deleteSQl, obj,keyType);
		
	}
	
	
	
	
	@Transactional(readOnly = true, propagation = Propagation.NOT_SUPPORTED)
	public TotalResult<DicVO> queryPage(final int start, final int limit,
			final DicVO dto,final String where, final JdbcForDTO... e) {
		
		
		return (TotalResult<DicVO>) super.getJdbcTemplate().execute(
				new ConnectionCallback() {
					public Object doInConnection(Connection conn)
							throws SQLException {
						return qdao.queryPage(conn, start, limit, dto,where, e);
					}
				});

		
	}
	@Transactional(readOnly = true, propagation = Propagation.NOT_SUPPORTED)
	public List<DicVO> query(final DicVO dto,final String where, final JdbcForDTO... e) {
		
		
		return (List<DicVO>) super.getJdbcTemplate().execute(
				new ConnectionCallback() {
					public Object doInConnection(Connection conn)
							throws SQLException {
						return qdao.query(conn, dto,where, e);
					}
				});

		
	}
	
	
	
	public String getWhere(String colname,int length){
		StringBuffer sql = new StringBuffer(100);
		for (int i = 0; i < length; i++) {

			sql.append(colname).append("=").append("?");
			
			if (i != length- 1) {
				
				sql.append(" or ");
			}
		}
		return sql.toString();
	}
}
